package jdbc;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;


/**
 * Created with IntelliJ IDEA.
 * Description:
 * <p>
 * 操作JDBC的工具类，封装了一些工具方法
 *
 * @author lzy
 * @version v1.0
 * @create 2018-07-30 上午11:34
 */
public class JdbcUtils {
    private static final Logger LOGGER = LoggerFactory.getLogger(JdbcUtils.class);
    private static DataSource dataSource = null;

    // 数据库连接池只需要初始化一次
    static {
//        dataSource = new ComboPooledDataSource("helloc3p0");
        Properties properties = new Properties();
        try {
            properties.load(JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 处理数据库事务
    public static void commit(Connection connection) {
        if (connection != null) {
            try {
                connection.commit();
            } catch (SQLException e) {
                LOGGER.error("commit error", e);
            }
        }
    }

    // 回滚事务
    public static void rollback(Connection connection) {
        if (connection != null) {
            try {
                connection.rollback();
            } catch (SQLException e) {
                LOGGER.error("rollback error", e);
            }
        }
    }

    // 开始事务
    public static void beginTransaction(Connection connection) {
        if (connection != null) {
            try {
                connection.setAutoCommit(false);
            } catch (SQLException e) {
                LOGGER.error("begin transaction error", e);
            }
        }
    }

    /**
     * 执行sql语句，可以传递参数
     * sql语句类型包括INSERT，UPDATE和DELETE，而不包含SELECT
     *
     * @param sql
     * @param args
     */
    public static void update(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                statement.setObject(i + 1, args[i]);
            }
            statement.executeUpdate();
        } catch (Exception e) {
            LOGGER.error("update error", e);
        } finally {
            release(connection, statement);
        }
    }

    /**
     * 执行sql语句
     * sql语句类型包括INSERT，UPDATE和DELETE，而不包含SELECT
     *
     * @param sql
     */
    public static void update(String sql) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            statement.executeUpdate(sql);
        } catch (Exception e) {
            LOGGER.error("update error", e);
        } finally {
            release(connection, statement);
        }
    }

    /**
     * 关闭Statement和Connection
     *
     * @param connection
     * @param statement
     */
    public static void release(Connection connection, Statement statement) {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                LOGGER.error("close statement error", e);
            }
        }
        // 5.关闭链接
        // 数据库连接池的Connection对象进行close操作时，
        // 并不是真正的关闭，而是吧数据连接归还到数据库连接池中
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                LOGGER.error("close connection error", e);
            }
        }
    }

    public static void release(Connection connection, Statement statement, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                LOGGER.error("close statement error", e);
            }
        }
        // 5.关闭链接
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                LOGGER.error("close connection error", e);
            }
        }
    }

    /**
     * 通过数据库连接池获取连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 1. 获取连接的方法
     * 通过读取配置文件从数据库服务器获取一个连接
     *
     * @return
     * @throws SQLException
     * @throws IOException
     * @throws ClassNotFoundException
     */
    public static Connection getConnectionManually() throws SQLException, IOException, ClassNotFoundException {
        // 1.1 创建Properties对象
        Properties property = new Properties();
        // 1.2 获取jdbc.properties对应的输入流
        InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        // 1.3 加载2对应的输入流
        property.load(in);
        // 1.4 取出四个属性配置
        String driverClass = property.getProperty("db.driver");
        String url = property.getProperty("db.url");
        String user = property.getProperty("user");
        String password = property.getProperty("db.password");

        // 2.加载数据库驱动(对应的Driver实现类中有注册驱动的静态代码块)
        Class.forName(driverClass);

        // 3.通过DriverManager的getConnection（）方法获取数据库链接
        return DriverManager.getConnection(url, user, password);
    }

}
